﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;

/// <summary>
/// DataBaseAccess の概要の説明です
/// </summary>
public class DataBaseAccess
{
    protected SqlConnection objDb;
    public DataBaseAccess(string connectionString)
    {
        objDb = new SqlConnection(connectionString);
    }

    public bool WriteLog(DateTime time, string group, string sender, string dest, string log)
    {
        string _gr = Sanitizing.SafeSQLLiteral(group);
        string _se = Sanitizing.SafeSQLLiteral(sender);
        string _de = Sanitizing.SafeSQLLiteral(dest);
        string _log = Sanitizing.SafeSQLLiteral(log);

        SqlCommand objCom = new SqlCommand("INSERT INTO LogTable (timestamp, usergroup, sender, destination, message) VALUES (@timestamp, @usergroup, @sender, @destination, @message)", objDb);
        objCom.Parameters.AddWithValue("@timestamp", time);
        objCom.Parameters.AddWithValue("@usergroup", _gr);
        objCom.Parameters.AddWithValue("@sender", _se);
        objCom.Parameters.AddWithValue("@destination",_de);
        objCom.Parameters.AddWithValue("@message", _log);
        int affectedRows = 0;
        try
        {
            objDb.Open();
            affectedRows = objCom.ExecuteNonQuery();
        }
        finally
        {
            objDb.Close();
        }

        if (affectedRows != 1) return false;
        return true;
    }
    public void ReadLog(DateTime lastUpdate, string group,out List<WebService.KMessage> outList)
    {
        string _gr = Sanitizing.SafeSQLLiteral(group);
        outList = new List<WebService.KMessage>();

        SqlCommand objCom = new SqlCommand("SELECT * FROM LogTable WHERE timestamp>@timestamp AND usergroup=@usergroup", objDb);
        objCom.Parameters.AddWithValue("@timestamp", lastUpdate);
        objCom.Parameters.AddWithValue("@usergroup", _gr);
        //WebService.KMessage km;
        try
        {
            objDb.Open();
            SqlDataReader objDr = objCom.ExecuteReader();
            while (objDr.Read())
            {
                //km = new WebService.KMessage();
                //km.Time = (DateTime)objDr["time"];
                //km.Group = (string)objDr["usergroup"];
                //km.SenderName = (string)objDr["sender"];
                //km.DestName = (string)objDr["destination"];
                //km.Log = (string)objDr["message"];
                //outList.Add(km);
                outList.Add(new WebService.KMessage(
                    (DateTime)objDr["timestamp"],
                    (string)objDr["usergroup"],
                    (string)objDr["sender"],
                    (string)objDr["destination"],
                    (string)objDr["message"]));

            }
            objDr.Close();
        }
        finally
        {
            objDb.Close();
        }
    }
    public bool DeleteAll()
    {
        SqlCommand objCom = new SqlCommand("DELETE LogTable", objDb);
        int affectedRows = 0;
        try
        {
            objDb.Open();
            objCom.ExecuteNonQuery();
        }
        finally
        {
            objDb.Close();
        }

        return true;
    }

    //public bool PassAuthenticate(string id, string pass)
    //{
    //    SqlCommand objCom = new SqlCommand("SELECT * FROM Table1 WHERE ID=@ID AND pass=@pass", objDb);
    //    objCom.Parameters.AddWithValue("@ID", id);
    //    objCom.Parameters.AddWithValue("@pass", pass);
    //    bool auth = false;
    //    try
    //    {
    //        objDb.Open();
    //        SqlDataReader objDr = objCom.ExecuteReader();
    //        if (objDr.Read())
    //        {
    //            // 検索の結果、該当するレコードが存在した場合、認証は成功
    //            auth = true;
    //        }
    //        else
    //        {
    //            auth = false;
    //        }
    //        objDr.Close();
    //    }
    //    finally
    //    {
    //        objDb.Close();
    //    }

    //    return auth;
    //}

    //public bool DeleteById(string id, string pass)
    //{
    //    int _id;
    //    if (!int.TryParse(id, out _id)) return false;

    //    //実際はStateを削除（４）にするだけ
    //    SqlCommand objCom = new SqlCommand("UPDATE Table1 SET state='4', timestamp = @timestamp WHERE ID=@ID AND pass=@pass", objDb);
    //    //SqlCommand objCom = new SqlCommand("DELETE Table1 WHERE ID=@ID AND pass=@pass", objDb);
    //    objCom.Parameters.AddWithValue("@ID", id);
    //    objCom.Parameters.AddWithValue("@pass", pass);
    //    objCom.Parameters.AddWithValue("@timestamp", GetTimeStamp());
    //    int affectedRows = 0;
    //    try
    //    {
    //        objDb.Open();
    //        affectedRows = objCom.ExecuteNonQuery();
    //    }
    //    finally
    //    {
    //        objDb.Close();
    //    }

    //    if (affectedRows != 1) return false;
    //    return true;

    //}

    protected string GetPass(string seed){
        //TODO: もっと激しく暗号化せよ
        string pass = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(seed, "MD5");
        //pass = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(pass, "SHA1");      
        return pass;
    }

    //時間切れは1日
    public int TimeTickToExpire = 24*60*60*1000*10;// 時間x分x秒x msec x 100 n sec
    //public int TimeTickToExpire = 1000 * 10;// デバグ用。1秒

    //protected int CheckTimeOut()
    //{
    //    SqlCommand objCom = new SqlCommand("UPDATE Table1 SET state='5' WHERE state='1' AND timestamp < @timestamp", objDb);
    //    //objCom.Parameters.AddWithValue("@timestamp", GetTimeStamp() - TimeTickToExpire);
    //    objCom.Parameters.AddWithValue("@timestamp", new DateTime(GetTimeStamp().Ticks - TimeTickToExpire));
    //    int affectedRows = 0;
    //    try
    //    {
    //        objDb.Open();
    //        affectedRows = objCom.ExecuteNonQuery();
    //    }
    //    finally
    //    {
    //        objDb.Close();
    //    }

    //    return affectedRows;
    //}

    public static string GetCurrentFromAbsoluteUri(string absUri)
    {
        return ConfigurationManager.AppSettings["BaseUri"];

    }
}
